package zxq.rollcall.course.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import zxq.rollcall.course.entity.SystemSignTableEntity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import zxq.rollcall.course.entity.dto.db.CourseSignDTO;
import zxq.rollcall.course.entity.dto.db.StudentInfoDTO;
import zxq.rollcall.course.entity.dto.db.StudentSignDTO;
import zxq.rollcall.course.entity.dto.db.TeacherSignDTO;
import zxq.rollcall.course.entity.query.PageQuery;

import java.util.List;

/**
 * <p>
 * 用户签到得表 Mapper 接口
 * </p>
 *
 * @author Yanghu
 * @since 2020-01-22
 */
public interface SystemSignTableDao extends BaseMapper<SystemSignTableEntity> {

    /**
     * 统计
     * @param courseId
     * @return
     */
    @Select("<script>" +
            " select a.id as teacherSignId,count(case when b.is_sign = 1 then  1 end) as signNum," +
            " count(case when b.is_sign = 0 then 1 end) as notSignNum," +
            " count(b.is_sign) as totalSignNum,a.sign_day as signDate,c.course_name as courseName" +
            " from SYSTEM_TEACHER_SIGN_TABLE as a,SYSTEM_SIGN_TABLE as b,SYSTEM_COURSE_TABLE as c" +
            " where a.id = b.teacher_sign and a.course_id = c.id and a.course_id = #{courseId}" +
            " <if test = 'teacherSign != null'>" +
            " and a.id = #{teacherSign}" +
            " </if>" +
            " group by teacher_sign"+
            " </script>"
    )
    List<CourseSignDTO> queryCourseSign(@Param("courseId") Integer courseId,@Param("teacherSign") Integer teachSignId);

    /**
     * 统计
     * @param courseId
     * @param studentId
     * @return
     */
    @Select("<script>" +
            " select d.nick_name as nickName,count(case when b.is_sign = 1 then 1 end) as signNum," +
            " count(case when b.is_sign = 0 then 0 end) as notSignNum,count(b.is_sign) as totalNum" +
            " from SYSTEM_TEACHER_SIGN_TABLE as a,SYSTEM_SIGN_TABLE as b,SYSTEM_USER_TABLE as d" +
            " where b.teacher_sign = a.id and b.student_id = d.id  and a.course_id = #{courseId}" +
            " <if test = 'studentId != null'>" +
            " and b.student_id = #{studentId}" +
            " </if>" +
            " group by b.student_id" +
            " </script>")
    List<StudentSignDTO> queryStudentSign(@Param("courseId") Integer courseId, @Param("studentId")Integer studentId);

    /**
     * 详情
     * @return
     */
    @Select("<script>" +
            " select c.course_name,a.sign_day,b.is_sign from SYSTEM_TEACHER_SIGN_TABLE as a,SYSTEM_SIGN_TABLE as b,SYSTEM_COURSE_TABLE as c" +
            " where a.id = b.teacher_sign and a.course_id = c.id and b.student_id = #{studentId}" +
            " <if test = 'courseId != null'>" +
            " and a.course_id = #{courseId}" +
            " </if>" +
            " order by  a.id desc" +
            " <if test = 'page != null'>" +
            " limit #{page.page},#{page.count}" +
            " </if>" +
            " </script>")
    List<StudentInfoDTO> queryStudentInfoSign(@Param("studentId")Integer studentId, @Param("courseId") Integer courseId,
                                              @Param("page") PageQuery pageQuery);


    /**
     * 查看某次签到得详情
     * @param teacherSignId
     * @return
     */
    @Select("SELECT b.nick_name,a.is_sign,a.start_sign_time,a.end_sign_time FROM SYSTEM_SIGN_TABLE as a,SYSTEM_USER_TABLE as b" +
            " where a.student_id = b.id and a.teacher_sign = #{teacher_sign}")
    List<TeacherSignDTO> queryTeacherSignDetail(@Param("teacher_sign") Integer teacherSignId);




}
